package org.sam.swing.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.Date;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 *
 * Description: Excel操作
 * 
 * CreateTime: 2017年12月11日 下午3:08:09
 *
 * Change History:
 *
 * Date CR Number Name Description of change
 *
 */
public class ExcelUtil {

	/**
	 * 2003以前格式
	 */
	private static final String EXCEL_XLS = "xls";

	/**
	 * 2007及其以后格式
	 */
	private static final String EXCEL_XLSX = "xlsx";

	/**
	 * 判断Excel的版本,获取Workbook
	 * 
	 * @param filename
	 *            文件
	 * @return
	 * @throws IOException
	 */
	public static Workbook getWorkbok(File file) throws Exception {
		checkExcelVaild(file);

		Workbook wb = null;
		FileInputStream in = new FileInputStream(file);
		if (file.getName().endsWith(EXCEL_XLS)) {
			// Excel 2003
			wb = new HSSFWorkbook(in);
		} else if (file.getName().endsWith(EXCEL_XLSX)) {
			// Excel 2007/2010
			wb = new XSSFWorkbook(in);
		}
		return wb;
	}

	/**
	 * 判断文件是否是excel
	 * 
	 * @throws Exception
	 */
	public static void checkExcelVaild(File file) throws Exception {
		if (!file.exists()) {
			throw new Exception("文件不存在");
		}
		if (!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))) {
			throw new Exception("文件不是Excel");
		}
	}

	/**
	 * 获取数据
	 * 
	 * @param cell
	 *            单元格
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public static Object getValue(Cell cell) {
		Object obj = null;
		switch (cell.getCellTypeEnum()) {
		case BOOLEAN:
			obj = cell.getBooleanCellValue();
			break;
		case ERROR:
			obj = cell.getErrorCellValue();
			break;
		case NUMERIC:
			obj = cell.getNumericCellValue();
			break;
		case STRING:
			obj = cell.getStringCellValue();
			break;
		default:
			break;
		}
		return obj;
	}
	
	/**
	 * 凑合用一下的格式化工具
	 */
	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

	/**
	 * 使用目标类型获取excel单元格值
	 * 
	 * @param cell
	 *            单元格
	 * @param targetType
	 *            目标类型
	 * @param defaultValue
	 *            默认值
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public static Object getValue(Cell cell, Class<?> targetType, Object defaultValue) {
		Object obj = null;
		if (cell == null)
			return null;
		
		switch (cell.getCellType()) {
		case BOOLEAN:
			obj = cell.getBooleanCellValue();
			break;
		case ERROR:
			obj = defaultValue;
			break;
		case NUMERIC:
			if (Byte.class.equals(targetType)) {
				obj = Byte.valueOf("" + cell.getNumericCellValue());
			} else if (Short.class.equals(targetType)) {
				obj = Short.valueOf("" + cell.getNumericCellValue());
			} else if (Integer.class.equals(targetType)) {
				obj = Integer.valueOf("" + cell.getNumericCellValue());
			} else if (Float.class.equals(targetType)) {
				obj = Float.valueOf("" + cell.getNumericCellValue());
			} else if (BigDecimal.class.equals(targetType)) {
				obj = BigDecimal.valueOf(cell.getNumericCellValue());
			} else {
				obj = cell.getNumericCellValue();
			}
			break;
		case STRING:
			if (Date.class.equals(targetType)) {
				try{
					obj= sdf.parse(cell.getStringCellValue());
				} catch(Exception ex){
					obj = null;
				}
			} else if (LocalDate.class.equals(targetType)) {
				obj = LocalDate.parse(cell.getStringCellValue());
			} else if (LocalDateTime.class.equals(targetType)) {
				obj = LocalDateTime.parse(cell.getStringCellValue());
			} else if (LocalTime.class.equals(targetType)) {
				obj = LocalTime.parse(cell.getStringCellValue());
			} else if (Byte.class.equals(targetType)) {
				obj = Byte.valueOf("" + cell.getStringCellValue());
			} else if (Short.class.equals(targetType)) {
				obj = Short.valueOf("" + cell.getStringCellValue());
			} else if (Integer.class.equals(targetType)) {
				obj = Integer.valueOf("" + cell.getStringCellValue());
			} else if (Float.class.equals(targetType)) {
				obj = Float.valueOf("" + cell.getStringCellValue());
			} else if (Double.class.equals(targetType)) {
				obj = Double.valueOf("" + cell.getStringCellValue());
			} else if (BigDecimal.class.equals(targetType)) {
				obj = new BigDecimal(cell.getStringCellValue());
			} else {
				obj = cell.getStringCellValue();
			}
			break;
		case BLANK:
			obj = defaultValue;
			break;
		case FORMULA:
			if (Date.class.equals(targetType)) {
				obj = cell.getDateCellValue();
			} else if (LocalDate.class.equals(targetType)) {
				obj = LocalDate.parse(cell.getDateCellValue().toLocaleString());
			} else if (LocalDateTime.class.equals(targetType)) {
				obj = LocalDateTime.parse(cell.getDateCellValue().toLocaleString());
			} else if (LocalTime.class.equals(targetType)) {
				obj = LocalTime.parse(cell.getDateCellValue().toLocaleString());
			} else if (Byte.class.equals(targetType)) {
				obj = Byte.valueOf("" + cell.getNumericCellValue());
			} else if (Short.class.equals(targetType)) {
				obj = Short.valueOf("" + cell.getNumericCellValue());
			} else if (Integer.class.equals(targetType)) {
				obj = Integer.valueOf("" + cell.getNumericCellValue());
			} else if (Float.class.equals(targetType)) {
				obj = Float.valueOf("" + cell.getNumericCellValue());
			} else if (BigDecimal.class.equals(targetType)) {
				obj = BigDecimal.valueOf(cell.getNumericCellValue());
			} else {
				obj = defaultValue;
			}
			break;
		case _NONE:
			obj = defaultValue;
		default:
			obj = defaultValue;
			break;
		}
		return obj;
	}

}
